package com.htht.smartschool.common.dao;

import java.math.BigDecimal;
import java.math.BigInteger;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.List;

import org.hibernate.Query;
import org.hibernate.Session;
import org.hibernate.Transaction;
import org.hibernate.jdbc.Work;
import org.springframework.orm.hibernate3.SessionFactoryUtils;

import com.htht.smartschool.common.model.Entity;
import com.htht.smartschool.util.config.DBConfig;
import com.htht.smartschool.util.system.ReflectionUtils;


/**
 * 数据查询基础DAO,提供查询指定数据库用户和默认数据库用户数据的接口
 *
 */
public class BaseDao {
		
	/**
	 * 获取默认数据库用户的Session
	 * @return
	 */
	public Session getSession() {
		return ((SessionManager)DataBaseManager.getInst().get(DBConfig.smartschool)).getSession();
	}
	
	/**
	 * 获取指定数据库用户的Session
	 * @param dbUser
	 * @return
	 */
	public Session getSession(String dbUser) {
		return ((SessionManager)DataBaseManager.getInst().get(dbUser)).getSession();
	}
	/**
	 * 关闭默认数据库用户Session
	 */
	public void closeSession() {
		((SessionManager)DataBaseManager.getInst().get(DBConfig.smartschool)).closeSession();
	}
 	/**
	 * 关闭指定数据库用户Session
	 * @param dbUser
	 */
	public void closeSession(String dbUser) {
		((SessionManager)DataBaseManager.getInst().get(dbUser)).closeSession();
	}
	/**
	 * 默认数据库用户新增实例
	 * @param ent
	 * @return
	 */
	public Entity create(Entity ent) {
		try {
			((SessionManager)DataBaseManager.getInst().get(DBConfig.smartschool)).add(ent);
			return ent;
		} catch (Exception e) {
			e.printStackTrace();
		}
		return null;
	}
	/**
	 * 指定数据库用户新增实例
	 * @param ent
	 * @param user
	 * @return
	 */
	public Entity create(Entity ent,String user) {
		try {
			((SessionManager)DataBaseManager.getInst().get(user)).add(ent);
			return ent;
		} catch (Exception e) {
			e.printStackTrace();
		}
		return null;
	}
	
	/**
	 * 默认数据库用户删除实例
	 * @param ent
	 * @return
	 */
	public boolean delete(Entity ent) {
		try {
			((SessionManager)DataBaseManager.getInst().get(DBConfig.smartschool)).delete(ent);
			return true;
		} catch (Exception e) {
			e.printStackTrace();
		}
		return false;
	}
	/**
	 * 指定数据库用户删除实例
	 * @param ent
	 * @param user
	 * @return
	 */
	public boolean delete(Entity ent,String user) {
		try {
			((SessionManager)DataBaseManager.getInst().get(user)).delete(ent);
			return true;
		} catch (Exception e) {
			e.printStackTrace();
		}
		return false;
	}
	/**
	 * 更新默认数据库用户实例
	 * @param ent
	 * @return
	 */
	public Entity update(Entity ent) {
		Entity result = null;
		try {
			((SessionManager)DataBaseManager.getInst().get(DBConfig.smartschool)).update(ent);
			result = ent;
		} catch (Exception e) {
			e.printStackTrace();
		}
		return result;
	}
	/**
	 * 更新指定用户实体
	 * @param ent
	 * @param user
	 * @return
	 */
	public Entity update(Entity ent,String user) {
		Entity result = null;
		try {
			((SessionManager)DataBaseManager.getInst().get(DBConfig.smartschool)).update(ent);
			result = ent;
		} catch (Exception e) {
			e.printStackTrace();
		}
		return result;
	}
	
	/**
	 * 执行hql语句查询，并取得第一个查询结果
	 * @param hql
	 * @return
	 */
	@SuppressWarnings("unchecked")
	public Entity queryFirstByHQL(String hql) {
		Entity result = null;
		try {
			getSession().clear();
			Query query = getSession().createQuery(hql);
			query.setFirstResult(0);
			query.setMaxResults(1);
			List<Entity> list = query.list();
			if (list != null && list.size() > 0) {
				result = (Entity)list.get(0);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			closeSession();
		}
		return result;
	}
	@SuppressWarnings("unchecked")
	public Entity queryFirstByHQL(String hql,Object obj) {
		Entity result = null;
		try {
			getSession().clear();
			Query query = getSession().createQuery(hql);
			query = ReflectionUtils.reflectQuery(query.getNamedParameters(), obj,query);
			query.setFirstResult(0);
			query.setMaxResults(1);
			List<Entity> list = query.list();
			if (list != null && list.size() > 0) {
				result = (Entity)list.get(0);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			closeSession();
		}
		return result;
	}
	
	/**
	 * 执行hql语句查询，并取得所有查询结果
	 * @param hql
	 * @return
	 */
	@SuppressWarnings("rawtypes")
	public List queryByHQL(String hql,Object obj) {
		List result = null;
		try {
			Query query = getSession().createQuery(hql);
			query = ReflectionUtils.reflectQuery(query.getNamedParameters(), obj,query);
			result = query.list();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			closeSession();
		}
		return result;
	}

	/**
	 * 执行hql语句分页查询，取得分页查询结果
	 * @param hql
	 * @param start
	 * @param len
	 * @return
	 */
	@SuppressWarnings("rawtypes")
	public List queryByHQLPage(String hql,Object obj,Long currentpage,Long pagesize) {
		List result = null;
		try {
			Query query = getSession().createQuery(hql);
			query = ReflectionUtils.reflectQuery(query.getNamedParameters(), obj,query);
			query.setFirstResult((currentpage.intValue()-1)*pagesize.intValue());
			query.setMaxResults(pagesize.intValue());
			result = query.list();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			closeSession();
		}
		return result;
	}
	/**
	 * 执行hql语句，查询结果个数
	 * @param hql
	 * @param user
	 * @return
	 */
	@SuppressWarnings("rawtypes")
	public int getCountByHqlPage(String hql,Object obj) {
		try {
			Query query = getSession().createQuery(hql);
			query = ReflectionUtils.reflectQuery(query.getNamedParameters(), obj,query);
			List list = query.list();
			return list.size();
		} catch (Exception e) {
			e.printStackTrace();
		}finally {
			closeSession();
		}
		return 0;
	}
	@SuppressWarnings("rawtypes")
	public int getCountBySqlPage(String sql,Object obj){
		BigInteger count = null;
		try {
			Query query = getSession().createSQLQuery(sql);
			query = ReflectionUtils.reflectQuery(query.getNamedParameters(), obj,query);
			List list = query.list();
			if (list.size()==1) {
				count = (BigInteger) list.get(0);
			}			
			return count.intValue();
		} catch (Exception e) {
			e.printStackTrace();
		}finally {
			closeSession();
		}
		return 0;
	}
	@SuppressWarnings("rawtypes")
	public int getCountBySqlPage(String sql){
		BigInteger count = null;
		try {
			Query query = getSession().createSQLQuery(sql);
			List list = query.list();
			if (list.size()==1) {
				count = (BigInteger) list.get(0);
			}			
			return count.intValue();
		} catch (Exception e) {
			e.printStackTrace();
		}finally {
			closeSession();
		}
		return 0;
	}
	/**
	 * 执行sql语句查询，取得所有查询结果
	 * @param sql
	 * @return
	 */
	@SuppressWarnings("rawtypes")
	public List queryBySQL(String sql) {
		List list = null;
		try {
 			list = getSession().createSQLQuery(sql).list();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			closeSession();
		}
		return list;
	}
	@SuppressWarnings("rawtypes")
	public List queryBySQL(String sql,Object obj) {
		List list = null;
		try {
			Query query = getSession().createSQLQuery(sql);
			query = ReflectionUtils.reflectQuery(query.getNamedParameters(), obj,query);
 			list = query.list();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			closeSession();
		}
		return list;
	}
	/**
	 * 执行sql语句查询，取得分页查询结果
	 * @param sql
	 * @param currentpage
	 * @param pageSize
	 * @return
	 */
	@SuppressWarnings("rawtypes")
	public List queryBySqlPage(String sql,Object obj,Long currentpage,Long pagesize){
		List list = null;
		try {
			Query query = getSession().createSQLQuery(sql);
			query = ReflectionUtils.reflectQuery(query.getNamedParameters(), obj,query);
			query.setFirstResult((currentpage.intValue()-1)*pagesize.intValue());
			query.setMaxResults(pagesize.intValue());
			list = query.list();
		} catch (Exception e) {
		}finally {
			closeSession();
		}
		return list;
	}
	@SuppressWarnings("rawtypes")
	public List queryBySqlPage(String sql,  Long currentpage,
			Long pagesize) {
		try {
			Query query = getSession().createSQLQuery(sql);
			query.setFirstResult((currentpage.intValue() - 1)
					* pagesize.intValue());
			query.setMaxResults(pagesize.intValue());
			List list = query.list();
			return list;
		} catch (Exception e) {
		} finally {
			closeSession();
		}
		return null;
	}
	static ResultSet rs = null;
	/**
	 * 执行sql语句查询，取得所有查询结果，类型为ResultSet
	 * @param sql
	 * @return
	 */
	public ResultSet queryBySQLRS(final String sql) {
		rs = null;
		try {
			getSession().doWork(new Work() {
				@Override
				public void execute(Connection conn)
						throws SQLException {
					PreparedStatement preparedStatement=conn.prepareStatement(sql);
                    rs = preparedStatement.executeQuery();
				}
			});
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			closeSession();
		}
		return rs;
	}

	/**
	 * 执行sql语句查询，取得所有查询结果，并指定返回结果的父类
	 * @param ent
	 * @param sql
	 * @return
	 */
	@SuppressWarnings("rawtypes")
	public List queryBySQLToEntity(Entity ent,String sql) {
		List list = null;
		try {
			list = getSession().createSQLQuery(sql).addEntity(ent.getClass()).list();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			closeSession();
		}
		return list;
	}
	/**
	 * 执行sql语句，主要用于增加、删除和修改
	 * @param ent
	 * @param sql
	 * @return
	 */
	public int executeSQL(String sql){
		int i = 0;
		try { 
			Transaction transaction = getSession().beginTransaction();
			i = getSession().createSQLQuery(sql).executeUpdate();
			transaction.commit();
		} catch (Exception e) {
			e.printStackTrace();
			i = -1;
		}finally {
			closeSession();
		}
		return i;
	}
	public int executeSQL(String sql,Object obj){
		int i = 0;
		try { 
			Query query = getSession().createSQLQuery(sql);
			Transaction transaction = getSession().beginTransaction();
			query = ReflectionUtils.reflectQuery(query.getNamedParameters(), obj,query);
			i = query.executeUpdate();
			transaction.commit();
		} catch (Exception e) {
			e.printStackTrace();
			i = -1;
		}finally {
			closeSession();
		}
		return i;
	}
	/**
	 * 执行sql语句查询，获取查询结果次数,返回类型BigInteger
	 * @param ent
	 * @param sql
	 * @return
	 */
	@SuppressWarnings("rawtypes")
	public BigInteger getCountBySqlPage_BigInteger(String sql){
		BigInteger count = null;
		try {
			Query query = getSession().createSQLQuery(sql);
			List list = query.list();
			if (list.size()==1) {
				count = (BigInteger) list.get(0);
			}			
			return count;
		} catch (Exception e) {
			e.printStackTrace();
		}finally {
			closeSession();
		}
		return null;
	}
	
	/**
	 * 执行sql语句查询，获取查询结果次数,返回类型BigDecimal
	 * @param ent
	 * @param sql
	 * @return
	 */
	@SuppressWarnings("rawtypes")
	public BigDecimal getCountBySqlPage_BigDecimal(String sql){
		BigDecimal count = null;
		try {
			Query query = getSession().createSQLQuery(sql);
			List list = query.list();
			if (list.size()==1) {
				count = (BigDecimal) list.get(0);
			}			
			return count;
		} catch (Exception e) {
			e.printStackTrace();
		}finally {
			closeSession();
		}
		return null;
	}
	public int deleteSQL(String[] sql){
		int i = 0;
		Transaction transaction = getSession().beginTransaction();
		try { 
			for(int j=0;j<sql.length;j++){
				i = getSession().createSQLQuery(sql[j]).executeUpdate();
			}
			transaction.commit();
		} catch (Exception e) {
			e.printStackTrace();
			transaction.rollback();
			i = -1;
		}finally {
			closeSession();
		}
		return i;
	}
	public int deleteSQL(String[] sql,Object obj){
		int i = 0;
		Transaction transaction = getSession().beginTransaction();
		try { 
			for(int j=0;j<sql.length;j++){
				Query query = getSession().createSQLQuery(sql[j]);
				query = ReflectionUtils.reflectQuery(query.getNamedParameters(), obj,query);
				i = query.executeUpdate();
			}
			transaction.commit();
		} catch (Exception e) {
			e.printStackTrace();
			transaction.rollback();
			i = -1;
		}finally {
			closeSession();
		}
		return i;
	}
	public List queryByHQL(String hql) {
		List result = null;
		try {
			Query query = getSession().createQuery(hql);
			result = query.list();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			closeSession();
		}
		return result;
	}
	/**
	 * 批量添加方法
	 * @param queries
	 * @return 赵凯
	 */
	public boolean addBatchs(final List queries){
		getSession().doWork(  
				  new Work() { 
					 @Override
				    public void execute(Connection conn) {  
				      // 这里已经得到connection了，可以继续你的JDBC代码。  
				      // 注意不要close了这个connection。  
				    	try {
							Statement statemenet = conn.createStatement();
							conn.setAutoCommit(false);
							 for (int i =0;i<queries.size();i++) {
								    statemenet.addBatch(String.valueOf(queries.get(i)));
								}
							statemenet.executeBatch();
							conn.commit();
							statemenet.close();
						} catch (SQLException e) {
							// TODO Auto-generated catch block
							e.printStackTrace();
						}
				    }  
				  });
		return true;
	}
}
